package com.ajiao3.preparedstatement.crud;

import com.bean.Customer;
import com.util.JDBCUtil;
import org.junit.Test;
import sun.security.jca.JCAUtil;

import javax.print.attribute.standard.QueuedJobCount;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;

/**
 * 针对于Customer表的查询操作
 */
public class CustomerForQuery {

    public static void main(String[] args) {
        CustomerForQuery customerForQuery = new CustomerForQuery();
//      String sql = "select id,name,email,birth from customers where id = ?";

//      Customer customer = customerForQuery.queryForCustomers(sql,13);

        String sql1 = "select name,email from customers where name = ?";
        Customer customer1 = customerForQuery.queryForCustomers(sql1, "周杰伦");
        System.out.println(customer1);
    }



    /**
     * 针对于Customers表的通用查询操作
     */
    @Test
    public Customer queryForCustomers(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //1.获取数据库连接
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            //填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //执行，并返回结果集
            resultSet = ps.executeQuery();
            //获取结果集的元数据:ResultSetMetaData
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();

            if (resultSet.next()) {
                Customer cust = new Customer();
                //处理一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    //获取到每列字段的值
                    Object columnvalue = resultSet.getObject(i + 1);
                    //获取每个列的列名
                    String columnName = rsmd.getColumnName(i + 1);

                    //给cust对象指定的columnName属性，赋值为value，通过反射
                    //通过反射去找Customer类中对应columnName列名的属性，然后给他复制columnvalue
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);//拿到后这个属性可能是私有的，把它true
                    field.set(cust, columnvalue);//把这个属性名的值复制为
                }
                return cust;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.CloseResource(conn, ps, resultSet);
        }

        return null;
    }


    @Test
    public void testQuery1() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //1.获取数据库连接
            conn = JDBCUtil.getConnection();
            String sql = "select id,name,email,birth from customers where id=?";

            //2.预编译sql，返回preparedStatement的实例
            ps = conn.prepareStatement(sql);

            //3.填充占位符
            ps.setObject(1, 1);

            //4.执行,并返回接口集
            resultSet = ps.executeQuery();

            //5.处理接口集
            if (resultSet.next())//判断结果集下一条是否有数据，如果有返回true，并指针下移；反之
            {
                //6.获取当前这条数据的各个字段值
                int id = resultSet.getInt(1);//一表示查询到的第一个字段
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);
                //显示方式一
                //            System.out.println("id="+id+",name="+name+",email="+email+",date="+date);
                //方法二
                //            Object[]data =new Object[]{id,name,email,date};
                //方法三 将数据封装为一个对象
                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtil.CloseResource(conn, ps, resultSet);
        }
    }
}
